TOP

Fuzzy Lookup pentru LibreOffice Calc

FUZZYLOOKUP() Descriere

Cu toții cunoaștem binecunoscuta funcție VLOOKUP() care ne ajută să combinăm datele din diferite tabele. Cu toate acestea, această funcție are un dezavantaj semnificativ - nu poate combina valori similare, adică dacă există o eroare în cuvânt, atunci nu va exista nicio potrivire.

Pentru a putea combina valorile aproximative, putem crea propria noastră funcție. Să-i spunem FuzzyLookup().

Să ne imaginăm că avem două liste. Ambele au aproximativ aceleași elemente, dar pot fi scrise ușor diferit. Sarcina este de a găsi pentru fiecare element din prima listă cel mai asemănător element din a doua listă, adică. implementați o căutare pentru cel mai apropiat text maxim similar.

Marea întrebare, în acest caz, este ce să ia în considerare criteriul „asemănării”. Doar numărul de caractere care se potrivesc? Este numărul de meciuri consecutive? Ar trebui luate în considerare majuscule sau spații? Ce să faci cu aranjarea diferită a cuvintelor într-o frază? Există multe opțiuni și nu există o soluție unică - pentru fiecare situație una sau alta va fi mai bună decât altele.

În cazul nostru, implementăm cea mai simplă opțiune - căutarea după numărul maxim de potriviri de caractere. Nu este perfect, dar funcționează destul de bine în majoritatea situațiilor.


Cod StarBASIC pentru funcția FuzzyLookup

A adauga funcția FuzzyLookup , deschide meniul Tools - Macros - Edit Macros... , Selectați Module1 și copiați următorul text în modul:

  1. Function FuzzyLOOKUP(LookupValue As String, SrcTable As VariantOptional SimThreshold As SingleAs String   
  2.   ' moonexcel.com.ua   
  3.   Dim Str       As String    
  4.   Dim CellArray As Variant  
  5.   Dim StrArray  As Variant  
  6.     
  7.   If IsMissing(SimThreshold) Then SimThreshold  = 0  
  8.     
  9.   Str      = LCase(LookupValue)  
  10.   StrArray = Split(Str)  
  11.   StrExt   = UBound(StrArray)    
  12.               
  13.   For Each Cell In SrcTable  
  14.                             
  15.     CellArray = Split(LCase(Cell))  
  16.     CellExt   = UBound(CellArray)             
  17.     CellRate  = 0  
  18.       
  19.     ' Verificăm fiecare cuvânt din expresia de căutare   
  20.     For x = 0 To StrExt   
  21.       
  22.       StrWord = StrArray(x)     
  23.       If Len(StrWord) = 0 Then GoTo continue_x  
  24.       MaxStrWordRate = 0  
  25.         
  26.       ' Verificăm fiecare cuvânt din celula următoare din tabelul original de valori   
  27.       For i = 0 To CellExt  
  28.           
  29.         CellWord = CellArray(i)  
  30.         If Len(CellWord) = 0 Then GoTo continue_i  
  31.      
  32.         FindCharNum = OccurrenceNum(StrWord, CellWord)  
  33.         StrWordRate = FindCharNum / Max(Len(StrWord),Len(CellWord))  
  34.           
  35.         If StrWordRate > MaxStrWordRate Then MaxStrWordRate = StrWordRate  
  36.         continue_i:  
  37.       Next i              
  38.                           
  39.       CellRate = CellRate + MaxStrWordRate  
  40.       continue_x:  
  41.     Next x                 
  42.           
  43.     ' Păstrăm cel mai bun meci   
  44.     If CellRate > MaxCellRate Then      
  45.       MaxCellRate = CellRate  
  46.       BestCell    = Cell            
  47.          
  48.       FindCharNum = OccurrenceNum(Str, Cell)  
  49.       SimRate     = FindCharNum / Max(Len(Str),Len(Cell))  
  50.     End If         
  51.           
  52.   Next Cell  
  53.       
  54.   IF SimRate >= SimThreshold Then   
  55.     IF SimThreshold = -1 Then  
  56.       ReturnValue = BestCell + " (" + Format(SimRate, "0.00") + ")"  
  57.     ElseIf SimThreshold = -2 Then  
  58.       ReturnValue = Format(SimRate, "0.00")  
  59.     Else  
  60.       ReturnValue = BestCell  
  61.     End If  
  62.   Else   
  63.     ReturnValue = ""  
  64.   End If      
  65.     
  66.   FuzzyLOOKUP = ReturnValue  
  67. End Function  
  68.   
  69.   
  70. Function OccurrenceNum(ByVal SourceString As StringByVal TargetString As String)  
  71.   For i = 1 To Len(SourceString)                                          
  72.     ' Căutăm apariția fiecărui simbol   
  73.     Position = InStr(1, TargetString, Mid(SourceString, i, 1), 1)    
  74.     ' Mărim contorul de coincidențe   
  75.     If Position > 0 Then         
  76.       Count = Count + 1  
  77.       ' Eliminați simbolul găsit   
  78.       TargetString = Left(TargetString, Position - 1) + Right(TargetString, Len(TargetString) - Position)     
  79.     End If  
  80.   Next i      
  81.   OccurrenceNum = Count  
  82. End Function  
  83.   
  84.   
  85. Function Max(ByVal value1 As VariantByVal value2 As Variant)    
  86.   If value1 > value2 Then  
  87.     Result = value1  
  88.   Else  
  89.     Result = value2  
  90.   End If  
  91.   Max = Result  
  92. End Function  

Apoi, aproape Macro Editor și reveniți la foaia de lucru LibreOffice Calc - acum puteți folosi noua noastră funcție FuzzyLookup() .

Folosind extensia

După aceea, această funcție va fi disponibilă în toate fișierele care vor fi deschise în LibreOffice Calc.